package org.example;

import org.junit.Test;
import org.my.stock.util.MyUtil;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class MyH2 {
    @Test
    public void my_test() throws PropertyVetoException, SQLException, ClassNotFoundException {

        Connection connection = MyUtil.getConnection();
        connection.close();
        System.out.println("吴大富是大帅哥！");
    }

    @Test
    public void my_create_table() throws PropertyVetoException, SQLException, ClassNotFoundException {

        Connection connection = MyUtil.getConnection();
        Statement statement = connection.createStatement();

        String code = "CREATE TABLE IF NOT EXISTS stock_date (" +
                "  id INT PRIMARY KEY AUTO_INCREMENT," +
                //"  name VARCHAR(20) NOT NULL," +
                "  st_timestamp date," +
                "  volume float," +
                "  open float," +
                "  high float," +
                "  low float," +
                "  close float," +
                "  chg float," +
                "  percent float," +
                "  turnoverrate float," +
                "  amount float," +
                "  volume_post float," +
                "  amount_post float," +
                "  pe float," +
                "  pb float," +
                "  ps float," +
                "  pcf float," +
                "  market_capital float," +
                "  balance float," +
                "  hold_volume_cn float," +
                "  hold_ratio_cn float," +
                "  net_volume_cn float," +
                "  hold_volume_hk float," +
                "  hold_ratio_hk float," +
                "  net_volume_hk float," +
                "  symbol VARCHAR)";

        statement.execute(code);

        code = "CREATE TABLE IF NOT EXISTS stock_week (" +
                "  id INT PRIMARY KEY AUTO_INCREMENT," +
                //"  name VARCHAR(20) NOT NULL," +
                "  st_timestamp date," +
                "  volume float," +
                "  open float," +
                "  high float," +
                "  low float," +
                "  close float," +
                "  chg float," +
                "  percent float," +
                "  turnoverrate float," +
                "  amount float," +
                "  volume_post float," +
                "  amount_post float," +
                "  pe float," +
                "  pb float," +
                "  ps float," +
                "  pcf float," +
                "  market_capital float," +
                "  balance float," +
                "  hold_volume_cn float," +
                "  hold_ratio_cn float," +
                "  net_volume_cn float," +
                "  hold_volume_hk float," +
                "  hold_ratio_hk float," +
                "  net_volume_hk float," +
                "  symbol VARCHAR)";

        statement.execute(code);

        code = "CREATE TABLE IF NOT EXISTS stock_month (" +
                "  id INT PRIMARY KEY AUTO_INCREMENT," +
                //"  name VARCHAR(20) NOT NULL," +
                "  st_timestamp date," +
                "  volume float," +
                "  open float," +
                "  high float," +
                "  low float," +
                "  close float," +
                "  chg float," +
                "  percent float," +
                "  turnoverrate float," +
                "  amount float," +
                "  volume_post float," +
                "  amount_post float," +
                "  pe float," +
                "  pb float," +
                "  ps float," +
                "  pcf float," +
                "  market_capital float," +
                "  balance float," +
                "  hold_volume_cn float," +
                "  hold_ratio_cn float," +
                "  net_volume_cn float," +
                "  hold_volume_hk float," +
                "  hold_ratio_hk float," +
                "  net_volume_hk float," +
                "  symbol VARCHAR)";

        statement.execute(code);

        code = "CREATE TABLE IF NOT EXISTS stock_quarter (" +
                "  id INT PRIMARY KEY AUTO_INCREMENT," +
                //"  name VARCHAR(20) NOT NULL," +
                "  st_timestamp date," +
                "  volume float," +
                "  open float," +
                "  high float," +
                "  low float," +
                "  close float," +
                "  chg float," +
                "  percent float," +
                "  turnoverrate float," +
                "  amount float," +
                "  volume_post float," +
                "  amount_post float," +
                "  pe float," +
                "  pb float," +
                "  ps float," +
                "  pcf float," +
                "  market_capital float," +
                "  balance float," +
                "  hold_volume_cn float," +
                "  hold_ratio_cn float," +
                "  net_volume_cn float," +
                "  hold_volume_hk float," +
                "  hold_ratio_hk float," +
                "  net_volume_hk float," +
                "  symbol VARCHAR)";

        statement.execute(code);

        code = "CREATE TABLE IF NOT EXISTS stock_year (" +
                "  id INT PRIMARY KEY AUTO_INCREMENT," +
                //"  name VARCHAR(20) NOT NULL," +
                "  st_timestamp date," +
                "  volume float," +
                "  open float," +
                "  high float," +
                "  low float," +
                "  close float," +
                "  chg float," +
                "  percent float," +
                "  turnoverrate float," +
                "  amount float," +
                "  volume_post float," +
                "  amount_post float," +
                "  pe float," +
                "  pb float," +
                "  ps float," +
                "  pcf float," +
                "  market_capital float," +
                "  balance float," +
                "  hold_volume_cn float," +
                "  hold_ratio_cn float," +
                "  net_volume_cn float," +
                "  hold_volume_hk float," +
                "  hold_ratio_hk float," +
                "  net_volume_hk float," +
                "  symbol VARCHAR)";

        statement.execute(code);

        statement.close();
        connection.close();

        System.out.println("吴大富是大帅哥！");
    }

    @Test
    public void my_delete_table() throws PropertyVetoException, SQLException, ClassNotFoundException {

        Connection connection = MyUtil.getConnection();
        Statement statement = connection.createStatement();

        String code = "DROP TABLE IF EXISTS stock_date";
        statement.execute(code);

        code = "DROP TABLE IF EXISTS stock_week";
        statement.execute(code);

        code = "DROP TABLE IF EXISTS stock_month";
        statement.execute(code);

        code = "DROP TABLE IF EXISTS stock_quarter";
        statement.execute(code);

        code = "DROP TABLE IF EXISTS stock_year";
        statement.execute(code);

        statement.close();
        connection.close();

        System.out.println("吴大富是大帅哥！year");
    }

    @Test
    public void my_create_index() throws PropertyVetoException, SQLException, ClassNotFoundException {

        Connection connection = MyUtil.getConnection();
        Statement statement = connection.createStatement();

//        String code = "CREATE INDEX IF NOT EXISTS stock_date_name ON stock_date (name)";
//        statement.execute(code);

        String code = "CREATE INDEX IF NOT EXISTS stock_date_st_timestamp ON stock_date (st_timestamp)";
        statement.execute(code);

//        code = "CREATE INDEX IF NOT EXISTS stock_date_idx ON stock_date (name, st_timestamp)";
//        statement.execute(code);

//        code = "CREATE INDEX IF NOT EXISTS stock_week_name ON stock_week (name)";
//        statement.execute(code);

        code = "CREATE INDEX IF NOT EXISTS stock_week_st_timestamp ON stock_week (st_timestamp)";
        statement.execute(code);

//        code = "CREATE INDEX IF NOT EXISTS stock_week_idx ON stock_week (name, st_timestamp)";
//        statement.execute(code);

//        code = "CREATE INDEX IF NOT EXISTS stock_month_name ON stock_month (name)";
//        statement.execute(code);

        code = "CREATE INDEX IF NOT EXISTS stock_month_st_timestamp ON stock_month (st_timestamp)";
        statement.execute(code);

//        code = "CREATE INDEX IF NOT EXISTS stock_month_idx ON stock_month (name, st_timestamp)";
//        statement.execute(code);
//
//        code = "CREATE INDEX IF NOT EXISTS stock_quarter_name ON stock_quarter (name)";
//        statement.execute(code);

        code = "CREATE INDEX IF NOT EXISTS stock_quarter_st_timestamp ON stock_quarter (st_timestamp)";
        statement.execute(code);

//        code = "CREATE INDEX IF NOT EXISTS stock_quarter_idx ON stock_quarter (name, st_timestamp)";
//        statement.execute(code);
//
//        code = "CREATE INDEX IF NOT EXISTS stock_year_name ON stock_year (name)";
//        statement.execute(code);

        code = "CREATE INDEX IF NOT EXISTS stock_year_st_timestamp ON stock_year (st_timestamp)";
        statement.execute(code);

//        code = "CREATE INDEX IF NOT EXISTS stock_year_idx ON stock_year (name, st_timestamp)";
//        statement.execute(code);

        statement.close();
        connection.close();

        System.out.println("吴大富是大帅哥！year");
    }
}































